import os
import pandas as pd

def read_excel_sheets(directory,filename):

    file_path = os.path.join(directory, filename)
    print(f"读取文件: {filename}")
    
    # 使用pandas读取Excel文件
    xls = pd.ExcelFile(file_path)
    
    # 遍历每个Sheet页
    for sheet_name in xls.sheet_names:
        print(f"Sheet页名称: {sheet_name}")
        
        # 读取当前Sheet页的数据
        df = pd.read_excel(file_path, sheet_name=sheet_name)
        
        # 输出表头信息
        headers = df.columns.tolist()
        print(f"表头信息: {headers}")
        print("-" * 40)

        # 删除全为空的行和列
        df_cleaned = df.dropna(how='all').dropna(axis=1, how='all').replace()
        print(f"df_cleaned: {df_cleaned}")
        
    return df_cleaned

#获取发文日期
def find_cells_with_target(df_cleaned, target_value):
    # 创建一个空列表，用于存储匹配的单元格信息
    matches = []
    
    # 遍历DataFrame的每一行和每一列
    for row_index, row in df_cleaned.iterrows():
        for col_index, value in row.items():
            # 检查当前单元格是否包含目标值
            if target_value in value:
                matches.append({
                    '行索引': row_index,
                    '列名': col_index,
                    '值': value
                })
    
    return matches

#寻找目标行索引
def get_data_below_target(df_cleaned):
    # 遍历每一列，查找目标值
    for col in df_cleaned.columns:
        # 找到目标值所在的行索引
        target_index = df_cleaned[df_cleaned[col] == "案件编号"].index
        
        if not target_index.empty:
            # 找到目标值所在的行索引（取第一个匹配的行）
            target_row_index = target_index[0]
            
            # 提取目标值以下的所有行
            data_below_target = df_cleaned.iloc[target_row_index]
            return data_below_target
    
    # 如果未找到目标值，返回空DataFrame和None
    return pd.DataFrame(), None



def get_columns_below_target(df_cleaned, target_value):
    # 使用 dict() 函数初始化
    value_info = dict()
    # 遍历每一列，查找目标值
    for col in df_cleaned.columns:
        # 检查当前列是否包含目标值
        if target_value in df_cleaned[col].values:
            # 找到目标值所在的列索引
            target_col_index = df_cleaned.columns.get_loc(col)
            
            # 提取目标列右侧的所有列
            columns_to_right = df_cleaned.iloc[:, target_col_index + 1:]
            return columns_to_right, col  # 返回右侧列的内容以及目标列名
    
    # 如果未找到目标值，返回空DataFrame和None
    return pd.DataFrame(), None

if __name__ == "__main__":
    # 指定目录路径
    directory = input("请输入要读取的目录路径: ")
    
        # 遍历指定目录下的所有文件
    for filename in os.listdir(directory):
        if filename.endswith('.xlsx') or filename.endswith('.xls'):
            # 调用函数读取Excel文件
            df_cleaned = read_excel_sheets(directory,filename)

            #发文日期获取
            da_for_pub=find_cells_with_target(df_cleaned,"发文日期")
            print(f"发文日期: {da_for_pub}")

            # 使用 dict() 函数初始化
            value_info = dict()

            # 添加键值对
            
            for col in df_cleaned.columns:
                
                get_columns_below_target(df_cleaned,)
                value_info['name'] = 'values'
                value_info['www'] = 'www.com'


        print("=" * 60)

